{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from py2neo import Graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "graph = Graph(host='10.237.27.115')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "locatedInDf = pd.read_csv('./finalLocatedInMatchedEntities.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(186, 5)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "locatedInDf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>org</th>\n",
       "      <th>loc</th>\n",
       "      <th>resolvedWith</th>\n",
       "      <th>sentences</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ABG Container Handling Private Limited</td>\n",
       "      <td>['Mumbai']</td>\n",
       "      <td>['ABG CONTAINER HANDLING PRIVATE LIMITED']</td>\n",
       "      <td>['&lt;ORG&gt;Pacific International Lines&lt;/ORG&gt; (&lt;ORG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>ACE Designers Limited</td>\n",
       "      <td>['Bangalore']</td>\n",
       "      <td>['ACE DESIGNERS LIMITED']</td>\n",
       "      <td>['&lt;ORG&gt;&lt;MISC&gt;SALEMDhirajlal Gandhi College of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Abhaya Narendra Lotha Foundation</td>\n",
       "      <td>['New Delhi']</td>\n",
       "      <td>['ABHAY NARENDRA LODHA FOUNDATION']</td>\n",
       "      <td>['Former &lt;MISC&gt;MP&lt;/MISC&gt; &lt;PER&gt;Madhu Goud Yaskh...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Accenture</td>\n",
       "      <td>['Bangalore', 'Bangalore']</td>\n",
       "      <td>['ACCENTURE INDIA PRIVATE LIMITED', 'ACCENTURE...</td>\n",
       "      <td>['&lt;ORG&gt;Accenture&lt;/ORG&gt;, &lt;LOC&gt;Bangalore&lt;/LOC&gt;, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Aditya Birla</td>\n",
       "      <td>['Mumbai']</td>\n",
       "      <td>['ADITYA BIRLA SCIENCE AND TECHNOLOGY COMPANY ...</td>\n",
       "      <td>[\"R. Chidambaram (centre), Principal &lt;MISC&gt;Sci...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                                     org  \\\n",
       "0           0  ABG Container Handling Private Limited   \n",
       "1           1                   ACE Designers Limited   \n",
       "2           2        Abhaya Narendra Lotha Foundation   \n",
       "3           3                               Accenture   \n",
       "4           4                            Aditya Birla   \n",
       "\n",
       "                          loc  \\\n",
       "0                  ['Mumbai']   \n",
       "1               ['Bangalore']   \n",
       "2               ['New Delhi']   \n",
       "3  ['Bangalore', 'Bangalore']   \n",
       "4                  ['Mumbai']   \n",
       "\n",
       "                                        resolvedWith  \\\n",
       "0         ['ABG CONTAINER HANDLING PRIVATE LIMITED']   \n",
       "1                          ['ACE DESIGNERS LIMITED']   \n",
       "2                ['ABHAY NARENDRA LODHA FOUNDATION']   \n",
       "3  ['ACCENTURE INDIA PRIVATE LIMITED', 'ACCENTURE...   \n",
       "4  ['ADITYA BIRLA SCIENCE AND TECHNOLOGY COMPANY ...   \n",
       "\n",
       "                                           sentences  \n",
       "0  ['<ORG>Pacific International Lines</ORG> (<ORG...  \n",
       "1  ['<ORG><MISC>SALEMDhirajlal Gandhi College of ...  \n",
       "2  ['Former <MISC>MP</MISC> <PER>Madhu Goud Yaskh...  \n",
       "3  ['<ORG>Accenture</ORG>, <LOC>Bangalore</LOC>, ...  \n",
       "4  [\"R. Chidambaram (centre), Principal <MISC>Sci...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "locatedInDf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "locatedInDf['loc'] = locatedInDf['loc'].apply(eval)\n",
    "locatedInDf['resolvedWith'] = locatedInDf['resolvedWith'].apply(eval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(153, 5)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#extracting rows in which companies are resolved with single organization in graph database\n",
    "df = locatedInDf[locatedInDf['resolvedWith'].apply(lambda x: len(x) == 1)]\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_query = 'match (c:city) where lower(c.name) = \"{}\" return c.uuid'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "London\n"
     ]
    }
   ],
   "source": [
    "uuids = {}\n",
    "uuids['Bangalore'] = 154538\n",
    "uuids['Bengaluru'] = 154538\n",
    "uuids['New Delhi'] = 154438\n",
    "uuids['Kanagala'] = 154337 #district is guntur\n",
    "uuids['Cochin'] = 154570 #district is ernakulam\n",
    "uuids['Kochi'] = 154570 #district is ernakulam\n",
    "uuids['Jamshedpur'] = 154519 #district is east singhbhum in jharkhand\n",
    "for i in range(df.shape[0]):\n",
    "    if df.iloc[i]['loc'][0] in uuids:\n",
    "        continue\n",
    "    res = graph.data(loc_query.format(df.iloc[i]['loc'][0].lower()))\n",
    "    if len(res) != 0:\n",
    "        uuids[df.iloc[i]['loc'][0]] = res[0]['c.uuid']\n",
    "    else:\n",
    "        #print(res, loc_query.format(df.iloc[i]['loc'][0]))\n",
    "        print(df.iloc[i]['loc'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "comp_uuid = []\n",
    "comp_query = 'match (c:company) where c.name = \"{}\" return c.uuid as uuid'\n",
    "for i in range(df.shape[0]):\n",
    "    try:\n",
    "        comp_uuid.append(graph.data(comp_query.format(df.iloc[i]['resolvedWith'][0]))[0]['uuid'])\n",
    "    except:\n",
    "        print(df.iloc[i]['resolvedWith'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "match (x{{uuid:{0}}}),(y{{uuid:{2} }}) merge (x)-[r:locatedin {{bidirectional:false, reltype:\"explicit\", source:\"news\", relid:{1}}}]-> (y) return r.relid as id\n"
     ]
    }
   ],
   "source": [
    "maxRelIdQuery = 'match ()-[r]-() return max(r.relid) as maxRelId'\n",
    "maxRelId = graph.data(maxRelIdQuery)[0]['maxRelId']\n",
    "insertlocatedinRelation = 'match (x{{uuid:{0}}}),(y{{uuid:{2} }}) merge (x)-[r:locatedin {{bidirectional:false, reltype:\"explicit\", source:\"news\", relid:{1}}}]-> (y) return r.relid as id'\n",
    "print (insertlocatedinRelation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Relation Id  617965\n",
      "AVESTHAGEN LIMITED Bangalore\n",
      "BHARAT PETROLEUM CORPORATION LIMITED Mumbai\n",
      "HINDUSTAN AERONAUTICS LIMITED Bangalore\n",
      "IBM INDIA PRIVATE LIMITED Bangalore\n",
      "INDIAN OIL CORPN. LIMITED Bangalore\n",
      "INFOSYS LIMITED Bangalore\n",
      "INFOSYS LIMITED Bangalore\n",
      "NATIONAL INSTRUMENTS LIMITED Bangalore\n",
      "ROOTS INDUSTRIES INDIA LIMITED Coimbatore\n",
      "TATA CONSULTANCY SERVICES LIMITED Bangalore\n",
      "London\n",
      "YOKOGAWA INDIA LIMITED Bangalore\n",
      "End RelId 618106\n"
     ]
    }
   ],
   "source": [
    "check_relation = 'match path=(c)-[:locatedin]-> (l) where c.uuid={} and l.uuid ={} return count(path) as c'\n",
    "relId = maxRelId + 1\n",
    "print('Starting Relation Id ', relId)\n",
    "for i in range(df.shape[0]):\n",
    "    try:\n",
    "        if graph.data(check_relation.format(comp_uuid[i], uuids[df.iloc[i]['loc'][0]]))[0]['c'] != 0:\n",
    "            print(df.iloc[i]['resolvedWith'][0], df.iloc[i]['loc'][0])\n",
    "        else:\n",
    "            res = graph.data(insertlocatedinRelation.format(comp_uuid[i], relId, uuids[df.iloc[i]['loc'][0]]))\n",
    "            if len(res) == 0:\n",
    "                print('Link not inserted')\n",
    "            else:\n",
    "                relId += 1\n",
    "    except KeyError:\n",
    "        print(df.iloc[i]['loc'][0])\n",
    "print('End RelId', relId)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "142"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "relId - maxRelId"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
